--    Author    : MAYANTING
--    Name      : ADM.RPT_SUST_DLMG_PAST_PRO_ORG_DEPB.HQL
--    Functions : 表42：存款余额分机构类型分产品统计表
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-06-12  MAYANTING           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE ADM.RPT_SUST_DLMG_PAST_PRO_ORG_DEPB PARTITION (DATA_DATE = '20180331')
    -- 存款产品类型
    SELECT
         AREA.AREA_CODE_4                                                        AS FIN_ORG_DIST
        ,DEP.CCY                                                                 AS CCY
        ,ORG.ORG_CODE                                                            AS ORG_TYP         -- 机构类型
        ,ORG.ORG_DSCR                                                            AS ORG_DESC        -- 机构类型描述
        ,PRO.DEPT_PRODUCT_ID_1                                                   AS BUSINESS_TYPE   -- 存款产品类型
        ,PRO.DEPT_PRODUCT_DSCR_1                                                 AS TYPE_DESC       -- 产品类型描述
        ,SUM(COALESCE(DEP.ACTUAL_BAL,0))/100000000                               AS ACTUAL_BAL      -- 存款余额
        ,SUM(COALESCE(DEP.BAL_LM,0))/100000000                                   AS BAL_LM          -- 余额上期末
        ,SUM(COALESCE(DEP.BAL_YF,0))/100000000                                   AS BAL_YF          -- 余额年初
        ,SUM(COALESCE(DEP.ACTUAL_BAL,0)-COALESCE(DEP.BAL_LC,0))/100000000        AS BAL_GROW        -- 同比增长
        ,SUM(COALESCE(DEP.WSUM_BAL,0))/100000000                                 AS WSUM_BAL        -- 余额加权值
        ,SUM(COALESCE(DEP.WBAL_LM,0))/100000000                                  AS WBAL_LM         -- 余额加权值上期末
        ,SUM(COALESCE(DEP.WBAL_YF,0))/100000000                                  AS WBAL_YF         -- 余额加权值年初
        ,SUM(COALESCE(DEP.WBAL_LC,0))/100000000                                  AS WBAL_LC         -- 余额加权值去年同期
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='20180331' AND DEPOSIT_TYPE IN ('D011','D012','D013','D014','D02','D03','D04','D051','D052','D061','D062','D063','D064','D065','D066','D067','D068','D069'))DEP
    -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON DEP.FIN_ORG_NO=REAL.ORG_ID AND '20180331' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '20180331' BETWEEN AREA.START_DATE AND AREA.END_DATE
    -- 与页面机构表关联
    LEFT JOIN DIMENSION.T_ORG_REPORT ORG ON DEP.FIN_ORG_NO=ORG.ORG_CODE
    -- 与存款产品类别表关联
    LEFT JOIN DIMENSION.DIM_DEPOSIT_PRODUCT PRO ON DEP.DEPOSIT_TYPE=PRO.DEPT_PRODUCT_ID AND '20180331' BETWEEN PRO.START_DATE AND PRO.END_DATE AND PRO.IS_VALID='1'
    GROUP BY   AREA.AREA_CODE_4
              ,DEP.CCY
              ,ORG.ORG_CODE
              ,ORG.ORG_DSCR
              ,PRO.DEPT_PRODUCT_ID_1
              ,PRO.DEPT_PRODUCT_DSCR_1

    UNION ALL

    -- 分机构不分产品合计
    SELECT
           AREA.AREA_CODE_4                                                        AS FIN_ORG_DIST
          ,DEP.CCY                                                                 AS CCY
          ,ORG.ORG_CODE                                                            AS ORG_TYP         -- 机构类型
          ,ORG.ORG_DSCR                                                            AS ORG_DESC        -- 机构类型描述
          ,'HJ'                                                                    AS BUSINESS_TYPE    -- 存款产品类型
          ,'合计'                                                                  AS TYPE_DESC        -- 产品类型描述
          ,SUM(COALESCE(DEP.ACTUAL_BAL,0))/100000000                               AS ACTUAL_BAL       -- 存款余额
          ,SUM(COALESCE(DEP.BAL_LM,0))/100000000                                   AS BAL_LM           -- 余额上期末
          ,SUM(COALESCE(DEP.BAL_YF,0))/100000000                                   AS BAL_YF           -- 余额年初
          ,SUM(COALESCE(DEP.ACTUAL_BAL,0)-COALESCE(DEP.BAL_LC,0))/100000000        AS BAL_GROW         -- 同比增长
          ,SUM(COALESCE(DEP.WSUM_BAL,0))/100000000                                 AS WSUM_BAL         -- 余额加权值
          ,SUM(COALESCE(DEP.WBAL_LM,0))/100000000                                  AS WBAL_LM          -- 余额加权值上期末
          ,SUM(COALESCE(DEP.WBAL_YF,0))/100000000                                  AS WBAL_YF          -- 余额加权值年初
          ,SUM(COALESCE(DEP.WBAL_LC,0))/100000000                                  AS WBAL_LC          -- 余额加权值去年同期
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='20180331')DEP
    -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON DEP.FIN_ORG_NO=REAL.ORG_ID AND '20180331' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '20180331' BETWEEN AREA.START_DATE AND AREA.END_DATE
    -- 与页面机构表关联
    LEFT JOIN DIMENSION.T_ORG_REPORT ORG ON DEP.FIN_ORG_NO=ORG.ORG_CODE
    GROUP BY  AREA.AREA_CODE_4
             ,DEP.CCY
             ,ORG.ORG_CODE
             ,ORG.ORG_DSCR;